﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Transactions;

namespace Test2
{
    public class AccesBazaDeDate
    {
        internal string UserName { get; set; }

        internal string Password { get; set; }

        internal string ServerName { get; set; }

        internal string DatabaseName { get; set; }

        internal string ConnectionString
        {
            get
            {
                //return "Data Source=" + ServerName + ";Initial Catalog=" +
                //    DatabaseName + ";Persist Security Info=True;User ID=" +
                //    UserName + ";Password=" + Password;
                return ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
            }
        }

        public List<string> autentificaUtilizator(string sql, SqlParameter[] pars)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                var cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(pars[0]);
                cmd.Parameters.Add(pars[1]);

                connection.Open();

                List<string> l = new List<string>();

                SqlDataReader rdr = cmd.ExecuteReader();
                while(rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                        l.Add(rdr[i].ToString());
                }
                rdr.Close();
                return l;
            }
        }

        public void creeazaUtilizator(string sql,SqlParameter nume,SqlParameter parola)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        SqlDataAdapter dataAdapter = new SqlDataAdapter();

                        dataAdapter.InsertCommand = new SqlCommand(sql, connection);
                        dataAdapter.InsertCommand.CommandType = CommandType.Text;
                        dataAdapter.InsertCommand.Transaction = tran;
                        dataAdapter.InsertCommand.Parameters.Add(nume);
                        dataAdapter.InsertCommand.Parameters.Add(parola);

                        dataAdapter.InsertCommand.ExecuteNonQuery();

                        tran.Commit();
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }

        public void stergeUtilizator(string sql, SqlParameter par)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        SqlDataAdapter dataAdapter = new SqlDataAdapter();

                        dataAdapter.DeleteCommand = new SqlCommand(sql, connection);
                        dataAdapter.DeleteCommand.CommandType = CommandType.Text;
                        dataAdapter.DeleteCommand.Transaction = tran;

                        dataAdapter.DeleteCommand.Parameters.Add(par);

                        dataAdapter.DeleteCommand.ExecuteNonQuery();

                        tran.Commit();
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }

        // public bool schimbaCredentiale(string sql);

        public void adaugaProdus(string sql,SqlParameter[] pars)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        SqlDataAdapter dataAdapter = new SqlDataAdapter();
                        dataAdapter.InsertCommand = new SqlCommand(sql, connection);
                        dataAdapter.InsertCommand.Transaction = tran;
                        dataAdapter.InsertCommand.CommandType = CommandType.Text;

                        foreach (var p in pars)
                            dataAdapter.InsertCommand.Parameters.Add(p);

                        dataAdapter.InsertCommand.ExecuteNonQuery();
                        tran.Commit();
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }

        public void adaugaPozaLaProdus(string sql,string sql2,SqlParameter PrNumeProdus,SqlParameter PrPoza)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(sql2,connection);

                        dataAdapter.UpdateCommand = new SqlCommand(sql, connection);
                        dataAdapter.UpdateCommand.CommandType = CommandType.Text;
                        dataAdapter.UpdateCommand.Transaction = tran;

                        dataAdapter.UpdateCommand.Parameters.Add(PrNumeProdus);
                       
                        DataTable tabelProduse = new DataTable();
                        dataAdapter.Fill(tabelProduse);

                        tran.Commit();
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }

        public void salveazaProdusEditat(string proceduraStocata,SqlParameter[] parametri)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(proceduraStocata, connection);

                        dataAdapter.UpdateCommand = new SqlCommand(proceduraStocata, connection);
                        dataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure;

                        foreach (var p in parametri)
                            dataAdapter.UpdateCommand.Parameters.Add(p);

                        dataAdapter.UpdateCommand.Transaction = tran;

                        dataAdapter.UpdateCommand.ExecuteNonQuery();

                        tran.Commit();
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }

        public bool produsExistentInBazaDeDate(string sqlProdus,SqlParameter par)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                var cmd = connection.CreateCommand();
                cmd.CommandText = sqlProdus;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(par);

                connection.Open();

                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    rdr.Close();
                    return true;
                }
                else
                {
                    rdr.Close();
                    return false;
                }
            }
        }

        public void stergeTemporarProdusul(string proceduraStocata,SqlParameter par)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        SqlDataAdapter dataAdapter = new SqlDataAdapter(proceduraStocata, connection);

                        dataAdapter.UpdateCommand = new SqlCommand(proceduraStocata, connection);
                        dataAdapter.UpdateCommand.CommandType = CommandType.StoredProcedure;

                      
                        dataAdapter.UpdateCommand.Parameters.Add(par);

                        dataAdapter.UpdateCommand.Transaction = tran;

                        dataAdapter.UpdateCommand.ExecuteNonQuery();

                        tran.Commit();
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }

        public void stergeDefinitivProdusul(string sqlStergere,SqlParameter par)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        SqlDataAdapter dataAdapter = new SqlDataAdapter();
                        dataAdapter.DeleteCommand = new SqlCommand(sqlStergere, connection);
                        dataAdapter.DeleteCommand.CommandType = CommandType.StoredProcedure;
                        dataAdapter.DeleteCommand.CommandText = sqlStergere;

                        dataAdapter.DeleteCommand.Parameters.Add(par);

                        dataAdapter.DeleteCommand.Transaction = tran;

                        dataAdapter.DeleteCommand.ExecuteNonQuery();

                        tran.Commit();
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }

        public void daPozaProdus(string queryPoza,SqlParameter par)
        {
            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection connection = new SqlConnection())
                {
                    connection.ConnectionString = ConnectionString;

                    // Assumes that connection is a valid SqlConnection object.

                    SqlCommand command = new SqlCommand(
                      queryPoza, connection);
                    

                    command.Parameters.Add(par);

                    // Writes the BLOB to a file (*.bmp).
                    FileStream stream;
                    // Streams the BLOB to the FileStream object.
                    BinaryWriter writer;

                    // Size of the BLOB buffer.
                    int bufferSize = 100;
                    // The BLOB byte[] buffer to be filled by GetBytes.
                    byte[] outByte = new byte[bufferSize];
                    // The bytes returned from GetBytes.
                    long retval;
                    // The starting position in the BLOB output.
                    long startIndex = 0;

                    // The publisher id to use in the file name.
                    string pubID = "";

                    // Open the connection and read data into the DataReader.
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

                    while (reader.Read())
                    {
                        // Get the publisher id, which must occur before getting the logo.
                        pubID = reader.GetString(0);

                        // Create a file to hold the output.
                        stream = new FileStream(
                          "logo" + pubID + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);
                        writer = new BinaryWriter(stream);

                        // Reset the starting byte for the new BLOB.
                        startIndex = 0;

                        // Read bytes into outByte[] and retain the number of bytes returned.
                        retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);

                        // Continue while there are bytes beyond the size of the buffer.
                        while (retval == bufferSize)
                        {
                            writer.Write(outByte);
                            writer.Flush();

                            // Reposition start index to end of last buffer and fill buffer.
                            startIndex += bufferSize;
                            retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);
                        }

                        // Write the remaining buffer.
                        writer.Write(outByte, 0, (int)retval - 1);
                        writer.Flush();

                        // Close the output file.
                        writer.Close();
                        stream.Close();
                    }

                    // Close the reader.
                    reader.Close();
                    
                }
                ts.Complete();
            }
        }

        public string cautaProdus(string procStoc,SqlParameter produs)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                var cmd = connection.CreateCommand();
                cmd.CommandText =procStoc ;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(produs);

                connection.Open();

                SqlDataReader rdr = cmd.ExecuteReader();
                
                if (rdr.Read())
                { 
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        sb.Append(rdr[i]);
                        sb.Append(" ");
                    }
                    rdr.Close();
                    return sb.ToString();
                }
                else
                    return null;
            }
        }

        public List<string> toateProduseleExistente(string procStoc)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        List<string> listaProduse = new List<string>();
                        StringBuilder sb = new StringBuilder();

                        //SqlDataAdapter dataAdapter = new SqlDataAdapter();
                        //dataAdapter.SelectCommand = new SqlCommand(procStoc, connection);
                        //dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                        //DataTable tabelProduse = new DataTable();
                        //dataAdapter.Fill(tabelProduse);
                        //return tabelProduse;

                        SqlCommand cmd = connection.CreateCommand();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "SELECT nume_produs,brand_id,disabled_ FROM Produse";
                        cmd.Transaction = tran;

                        var rdr = cmd.ExecuteReader();

                        while (rdr.Read())
                        {
                            for (int i = 0; i < rdr.FieldCount; i++)
                            {
                                if (rdr.IsDBNull(i) != true)
                                {
                                    sb.Append(rdr[i]);
                                    sb.Append(" ");
                                }
                            }
                            listaProduse.Add(sb.ToString());
                            sb.Clear();
                        }

                        rdr.Close();
                        return listaProduse;
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }

        public DataTable produseFiltrate(string queryProduse,string filtru)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        SqlDataAdapter dataAdapter = new SqlDataAdapter();
                        dataAdapter.SelectCommand = new SqlCommand(queryProduse, connection);
                        dataAdapter.SelectCommand.Transaction = tran;

                        DataTable tabelProduse = new DataTable();
                        dataAdapter.Fill(tabelProduse);

                        DataView view = new DataView(tabelProduse);
                        view.RowFilter = filtru;

                        return view.Table;
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }

        public DataTable produseSortate(string queryProduse,string criteriuSortare)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (SqlTransaction tran = connection.BeginTransaction())
                {
                    try
                    {
                        SqlDataAdapter dataAdapter = new SqlDataAdapter();
                        dataAdapter.SelectCommand = new SqlCommand(queryProduse, connection);
                        dataAdapter.SelectCommand.Transaction = tran;

                        DataTable tabelProduse = new DataTable();
                        dataAdapter.Fill(tabelProduse);

                        DataView view = new DataView(tabelProduse);
                        view.Sort = criteriuSortare;
                        return view.Table;
                    }
                    catch (Exception xcp)
                    {
                        tran.Rollback();
                        throw new Exception(xcp.ToString());
                    }
                }
            }
        }
    }

}
